package cn.conac.rc.monitor.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.conac.rc.framework.repository.GenericDao;
import cn.conac.rc.monitor.entity.VLocalMonitorBlbm;

@Repository
public interface VLocalMonitorBlbmRepository extends
        GenericDao<VLocalMonitorBlbm, String> {
    static final String sjtjBjListGroupByBlbm = "SELECT " + "t.blbm,"
            + "sum(t.sq_count) as sq_count," + "sum(t.sl_count) as sl_count,"
            + "sum(t.bsl_count) as bsl_count," + "sum(t.bj_count) as bj_count,"
            + "sum(t.xkbj_count) as xkbj_count,"
            + "sum(t.byxkbj_count) as byxkbj_count,"
            + "sum(t.zbbj_count) as zbbj_count,"
            + "sum(t.zzbj_count) as zzbj_count,"
            + "sum(t.bjcs_count) as bjcs_count, "
            + "sum(t.ycxbz_count) as ycxbz_count, "
            + "sum(t.bzs_count) as bzs_count, "
            + "sum(t.tbcxsq_count) as tbcxsq_count, "
            + "sum(t.tbcxcs_count) as tbcxcs_count, "
            + "sum(t.tbcxcscs_count) as tbcxcscs_count, "
            + "sum(t.bjysts_count) as bjysts_count, "
            + "sum(t.bzts_count) as bzts_count, "
            + "sum(t.tbcxts_count) as tbcxts_count, "
            + "sum(t.cksq_count) as cksq_count, "
            + "sum(t.wssq_count) as wssq_count, "
            + "'#' as dfsxbm,'#' as tj_date,'#' as xzqhbm ,sys_guid() as id"
            + " FROM V_LOCAL_MONITOR_Blbm T " + "where t.bjzt is not null "
            + "and t.xzqhbm=:areaCode "
            + "and t.tj_date BETWEEN :startDate AND :endDate"
            + " GROUP BY t.blbm   " + " ORDER BY t.blbm ASC NULLS LAST ";
    static final String sjtjListGroupByBlbm = "SELECT "
            + "t.blbm,"
            + "sum(t.sq_count) as sq_count,"
            + "sum(t.sl_count) as sl_count,"
            + "sum(t.bsl_count) as bsl_count,"
            + "sum(t.bj_count) as bj_count,"
            + "sum(t.xkbj_count) as xkbj_count,"
            + "sum(t.byxkbj_count) as byxkbj_count,"
            + "sum(t.zbbj_count) as zbbj_count,"
            + "sum(t.zzbj_count) as zzbj_count,"
            + "sum(t.bjcs_count) as bjcs_count, "
            + "sum(t.ycxbz_count) as ycxbz_count, "
            + "sum(t.bzs_count) as bzs_count, "
            + "sum(t.tbcxsq_count) as tbcxsq_count, "
            + "sum(t.tbcxcs_count) as tbcxcs_count, "
            + "sum(t.tbcxcscs_count) as tbcxcscs_count, "
            + "sum(t.bjysts_count) as bjysts_count, "
            + "sum(t.bzts_count) as bzts_count, "
            + "sum(t.tbcxts_count) as tbcxts_count, "
            + "sum(t.cksq_count) as cksq_count, "
            + "sum(t.wssq_count) as wssq_count, "
            + "'#' as dfsxbm,'#' as tj_date,'#' as xzqhbm ,sys_guid() as id"
            + " FROM V_LOCAL_MONITOR_Blbm T where t.xzqhbm like:areaCode and t.tj_date BETWEEN :startDate AND :endDate GROUP BY t.blbm   ORDER BY t.blbm ASC NULLS LAST ";
    static final String sjtjQuanjuListGroupByBlbm = "SELECT "
            + "t.blbm,"
            + "sum(t.sq_count) as sq_count,"
            + "sum(t.sl_count) as sl_count,"
            + "sum(t.bsl_count) as bsl_count,"
            + "sum(t.bj_count) as bj_count,"
            + "sum(t.xkbj_count) as xkbj_count,"
            + "sum(t.byxkbj_count) as byxkbj_count,"
            + "sum(t.zbbj_count) as zbbj_count,"
            + "sum(t.zzbj_count) as zzbj_count,"
            + "sum(t.bjcs_count) as bjcs_count, "
            + "sum(t.ycxbz_count) as ycxbz_count, "
            + "sum(t.bzs_count) as bzs_count, "
            + "sum(t.tbcxsq_count) as tbcxsq_count, "
            + "sum(t.tbcxcs_count) as tbcxcs_count, "
            + "sum(t.tbcxcscs_count) as tbcxcscs_count, "
            + "sum(t.bjysts_count) as bjysts_count, "
            + "sum(t.bzts_count) as bzts_count, "
            + "sum(t.tbcxts_count) as tbcxts_count, "
            + "sum(t.cksq_count) as cksq_count, "
            + "sum(t.wssq_count) as wssq_count, "
            + "'#' as dfsxbm,'#' as tj_date,'#' as xzqhbm ,sys_guid() as id"
            + " FROM V_LOCAL_MONITOR_Blbm T where t.xzqhbm=:areaCode and t.tj_date BETWEEN :startDate AND :endDate GROUP BY t.blbm   ORDER BY t.blbm ASC NULLS LAST ";
    static final String sjtjBjListGroupBySxbm = "SELECT "
            + "t.dfsxbm,"
            + "t.blbm,"
            + "sum(t.sq_count) as sq_count,"
            + "sum(t.sl_count) as sl_count,"
            + "sum(t.bsl_count) as bsl_count,"
            + "sum(t.bj_count) as bj_count,"
            + "sum(t.xkbj_count) as xkbj_count,"
            + "sum(t.byxkbj_count) as byxkbj_count,"
            + "sum(t.zbbj_count) as zbbj_count,"
            + "sum(t.zzbj_count) as zzbj_count,"
            + "sum(t.bjcs_count) as bjcs_count, "
            + "sum(t.ycxbz_count) as ycxbz_count, "
            + "sum(t.bzs_count) as bzs_count, "
            + "sum(t.tbcxsq_count) as tbcxsq_count, "
            + "sum(t.tbcxcs_count) as tbcxcs_count, "
            + "sum(t.tbcxcscs_count) as tbcxcscs_count, "
            + "sum(t.bjysts_count) as bjysts_count, "
            + "sum(t.bzts_count) as bzts_count, "
            + "sum(t.tbcxts_count) as tbcxts_count, "
            + "sum(t.cksq_count) as cksq_count, "
            + "sum(t.wssq_count) as wssq_count, "
            + "'#' as xzqhbm ,'#' as tj_date,sys_guid() as id"
            + " FROM V_LOCAL_MONITOR_Blbm T where t.bjzt is not null and t.xzqhbm=:areaCode and t.tj_date BETWEEN :startDate AND :endDate GROUP BY t.dfsxbm,t.blbm   ORDER BY t.dfsxbm,t.blbm ASC NULLS LAST ";
    static final String sjtjListGroupBySxbm = "SELECT "
            + "t.dfsxbm,"
            + "t.blbm,"
            + "sum(t.sq_count) as sq_count,"
            + "sum(t.sl_count) as sl_count,"
            + "sum(t.bsl_count) as bsl_count,"
            + "sum(t.bj_count) as bj_count,"
            + "sum(t.xkbj_count) as xkbj_count,"
            + "sum(t.byxkbj_count) as byxkbj_count,"
            + "sum(t.zbbj_count) as zbbj_count,"
            + "sum(t.zzbj_count) as zzbj_count,"
            + "sum(t.bjcs_count) as bjcs_count, "
            + "sum(t.ycxbz_count) as ycxbz_count, "
            + "sum(t.bzs_count) as bzs_count, "
            + "sum(t.tbcxsq_count) as tbcxsq_count, "
            + "sum(t.tbcxcs_count) as tbcxcs_count, "
            + "sum(t.tbcxcscs_count) as tbcxcscs_count, "
            + "sum(t.bjysts_count) as bjysts_count, "
            + "sum(t.bzts_count) as bzts_count, "
            + "sum(t.tbcxts_count) as tbcxts_count, "
            + "sum(t.cksq_count) as cksq_count, "
            + "sum(t.wssq_count) as wssq_count, "
            + "'#' as xzqhbm ,'#' as tj_date,sys_guid() as id"
            + " FROM V_LOCAL_MONITOR_Blbm T where t.xzqhbm=:areaCode and t.tj_date BETWEEN :startDate AND :endDate GROUP BY t.dfsxbm,t.blbm   ORDER BY t.dfsxbm,t.blbm ASC NULLS LAST ";

    @Query(value = sjtjBjListGroupByBlbm, nativeQuery = true)
    List<VLocalMonitorBlbm> getSjtjBjListGroupByBlbm(
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    /**
     * 查询本级 按部门分组数据统计办理数据
     */
    @Query(value = sjtjQuanjuListGroupByBlbm, nativeQuery = true)
    List<VLocalMonitorBlbm> getSjtjListGroupByBlbm(
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    /**
     * 查询全局 按部门分组数据统计办理数据
     */
    @Query(value = sjtjListGroupByBlbm, nativeQuery = true)
    List<VLocalMonitorBlbm> getQuanjuSjtjListGroupByBlbm(
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = sjtjBjListGroupBySxbm, nativeQuery = true)
    List<VLocalMonitorBlbm> getSjtjBjListGroupBySxbm(
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = sjtjListGroupBySxbm, nativeQuery = true)
    List<VLocalMonitorBlbm> getSjtjListGroupBySxbm(
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
}
